<?php

/**
 * 数据模型驱动
 * User: wz_zh
 * Date: 2017/9/6
 * Time: 9:56
 */

namespace PKFrame\Lib;
defined('PATH_PK') or die();

use PKFrame\Driver\MySQLi;
use PKFrame\Driver\PDO_MySQL;
use PKFrame\DataHandler\Arrays;

class DataBase
{
    private $_join = [];
    private $_where;
    private $_order;
    private $_group;
    private $_limit;
    private $_toList = false;
    private $_isBatch;
    private $_dbInitClass;
    // 当前数据库配置索引
    private $_nowDBLinkIndex = 0;

    // 数据库查询对象池
    protected static $links = [];
    // 数据库配置
    protected $connection_config = [];
    // 数据表名称
    protected $table;
    protected $sql = [];

    // 是否需要自动写入时间戳 如果设置为字符串 则表示时间字段的类型
    protected $autoWriteTimestamp;
    // 创建时间字段
    protected $createTime = 'create_time';
    // 更新时间字段
    protected $updateTime = 'update_time';
    // 时间字段取出后的默认时间格式
    protected $dateFormat;
    // 字段类型或者格式转换
    protected $type = [];
    // 是否为更新数据
    protected $isUpdate = false;
    public $is_install_DB;

    /**
     * DataBase constructor.
     * @param int $linkIndex
     */
    public function __construct($linkIndex = 0)
    {
        $this->is_install_DB = in_array(request()->controller(), ['ApiDataBase']);
        if (!$this->is_install_DB) {
            $this->_nowDBLinkIndex = $linkIndex;
            $this->SetDBLink($linkIndex);
        }
    }

    private function _notice($str)
    {
        out()->noticeByJson($str);
    }

    public function GetNowConfig($index = 0): array
    {
        $nowConfig = [];
        try {
            \request()->param('NowDBLinkIndex', $index);
            if (array_key_exists($index, $this->connection_config) && is_array($this->connection_config[$index])) {
                $nowConfig = $this->connection_config[$index];
            } else {
                $configList = cache()->Disk()->ReadByConfig('DataBase');
                if (empty($configList) || !is_array($configList)) {
                    throw new \Exception(language('DataBase_ConfigEmpty'));
                } else if (array_key_exists($index, $configList)) {
                    $this->connection_config[$index] = $configList[$index];
                    $nowConfig = $configList[$index];
                } else {
                    throw new \Exception(language('DataBase_Config_IndexEmpty'));
                }
            }
        } catch (\Exception $exception) {
            $this->_notice($exception->getMessage());
        }
        return $nowConfig;
    }

    public function SetConnectionConfig(array $params_config = null)
    {
        if (!is_null($params_config)) {
            $this->connection_config[$this->_nowDBLinkIndex] = $params_config;
        }
    }

    /**
     * 多链接的索引设置
     * @param int $index
     * @return $this
     */
    public function SetDBLink($index = 0): DataBase
    {
        $nowConfig = $this->GetNowConfig($index);
        try {
            if (is_array($this->_dbInitClass) && array_key_exists($index, $this->_dbInitClass)) {
                // 已经存在的初始化
                !method_exists($this->_dbInitClass[$index], 'checkPing') ?: $this->_dbInitClass[$index]->checkPing();
            } elseif (is_array($nowConfig) && array_key_exists('type', $nowConfig)) {
                is_array($this->_dbInitClass) ?: $this->_dbInitClass = [];
                $this->checkDataBaseParamsConfig($nowConfig, 'MySQL');
                if (extension_loaded('mysqli')) {
                    $this->_dbInitClass[$index] = new MySQLi($nowConfig, $this->_nowDBLinkIndex);
                } elseif (extension_loaded('pdo_mysql')) {
                    $this->_dbInitClass[$index] = new PDO_MySQL($nowConfig);
                } else {
                    throw new \Exception('MySQL extension not open');
                }
            }
        } catch (\Exception $exception) {
            $this->_notice($exception->getMessage());
        }
        return $this;
    }

    /**
     * 检查配置参数的完整性
     * @param $params_config
     * @param $db_type
     */
    public function checkDataBaseParamsConfig($params_config, $db_type)
    {
        $params_field = dict('Params_Field');
        try {
            foreach ($params_field[$db_type] as $field => $name) {
                if ((!array_key_exists($field, $params_config) || empty($params_config[$field]))) {
                    if ($db_type == 'MySQL' && $field == 'pass') {
                        continue;
                    }
                    throw new \Exception(
                        $name . '(' . $field . ')' . language('Is_Empty')
                    );
                }
            }
        } catch (\Exception $exception) {
            $this->_notice($exception->getMessage());
        }
    }


    public function checkDriverByMySQL($params_config, $index = 0)
    {
        try {
            !is_null($this->_nowDBLinkIndex) ?: $this->_nowDBLinkIndex = $index;
            if (extension_loaded('mysqli')) {
                $this->_dbInitClass[$this->_nowDBLinkIndex] = new MySQLi($params_config, $index);
            } elseif (extension_loaded('pdo_mysql')) {
                $this->_dbInitClass[$this->_nowDBLinkIndex] = new PDO_MySQL($params_config);
            } else {
                throw new \Exception('MySQL extension not open');
            }
            // 当参与对数据库名的操作时，不主动连接数据库名
            $is_connectDB = $this->is_install_DB ? false : true;
            $this->_dbInitClass[$this->_nowDBLinkIndex]->Conn($is_connectDB);
            if ($this->_dbInitClass[$this->_nowDBLinkIndex]->version < 50530) {
                throw new \Exception('The minimum version of MySQL is 5.5.3+');
            }
            $this->SetConnectionConfig($params_config);
        } catch (\Exception $exception) {
            $this->_notice($exception->getMessage());
        }
    }

    /**
     * 数据表的联表查询
     * @param $tableName
     * @param $condition_fromField
     * @param string $condition_joinField
     * @param int $linkIndex
     * @param string $joinType 连接的类型有： left join（左连接）, right join（右连接）, inner join（内连接）
     * @return $this
     */
    public function join($tableName, $condition_fromField, $condition_joinField = 'id', $linkIndex = 0, $joinType = 'left'): DataBase
    {
        $tableName_from = $this->getTableName();
        $tableName_join = $this->getTableName($tableName, $linkIndex);
        $this->_join[] = [
            'table' => $tableName_join,
            'condition_fromField' => $condition_fromField,
            'condition_joinField' => $condition_joinField,
            'joinType' => $joinType,
        ];
        return $this;
    }

    protected function getTableName($tableName = '', $linkIndex = 0): ?string
    {
        try {
            $tableName = strtolower(empty($tableName) ? $this->table : $tableName);
            $config = $this->GetNowConfig($linkIndex);
            if (is_array($config) && array_key_exists('type', $config)) {
                if (array_key_exists('prefix', $config) && !empty($config['prefix'])) {
                    return stristr($tableName, $config['prefix']) ? $tableName : "{$config['prefix']}{$tableName}";
                } else {
                    throw new \Exception(language('DataBase_Prefix_Empty'));
                }
            }
        } catch (\Exception $exception) {
            $this->_notice($exception->getMessage());
        }
        return null;
    }

    protected function getFrom(): string
    {
        return ' FROM ' . $this->getTableName();
    }

    /**
     * 组装条件SQL语句
     * @param array $options 选项数据
     * @param string $logic 关系词 And | Or
     * @return $this
     */
    public function Where(array $options = [], string $logic = 'and'): DataBase
    {
        if (Arrays::Is($options)) {
            $this->_where = ' WHERE ';
            $temp = [];
            foreach ($options as $key => $value) {
                if (is_numeric($key) && Arrays::Is($value) == 3) {
                    switch ($value[1]) {
                        case 'find_in_set':
                            $temp[] = 'find_in_set ("' . $value[2] . '",' . $value[0] . ')';
                            break;
                        case '!=':
                            $temp[] = $value[0] . ' NOT IN (' . (is_array($value[2]) ? implode(',', $value[2]) : $value[2]) . ')';
                            break;
                        default:
                            $temp[] = $value[0] . ' ' . $value[1] . ' ' . (is_numeric($value[2]) ? $value[2] : '"' . $value[2] . '"');
                            break;
                    }
                } elseif (Arrays::Is($value) || stristr($value, ',')) {
                    if (is_string($value)) {
                        $temp[] = $key . ' IN (' . $value . ')';
                    } else {
                        $v_arr = [];
                        foreach ($value as $v) {
                            // 判断是否为数字,非数字则要带上引号
                            $v_arr[] = is_numeric($v) ? $v : '"' . $v . '"';
                        }
                        $value = Arrays::Is($v_arr) ? implode(',', $v_arr) : '';
                        $temp[] = $key . ' IN (' . $value . ')';
                    }
                } else {
                    $temp[] = $this->_whereRelation($key, $value);
                }
            }
            $this->_where .= implode(' ' . strtoupper($logic) . ' ', $temp);
        }
        return $this;
    }

    public function WhereJoin($field, $value, $tableName = '', $relation = '=', $logic = 'AND'): DataBase
    {
        $tmp = $this->getTableName($tableName) . '.' . $field;
        if (is_numeric($value) || is_string($value)) {
            $tmp .= ' ' . $relation . ' ' . (is_numeric($value) ? $value : '"' . $value . '"');
        } elseif (is_array($value)) {
            $tmp .= " IN ('" . implode("','", $value) . "')";
        }
        $this->_where .= empty($this->_where) ? ' WHERE ' . $tmp : " {$logic} " . $tmp;
        return $this;
    }


    /**
     * @return mixed
     */
    public function GetWhere()
    {
        return $this->_where;
    }


    private function _whereRelation($key, $value): string
    {
        $result = '';
        $config = $this->connection_config[$this->_nowDBLinkIndex];
        if (isset($config['type'])) {
            switch (\gettype($value)) {
                case 'boolean':
                    $result = "{$key}=" . ($value ? "TRUE" : "FALSE");
                    break;
                default:
                    if (!is_array($value)) {
                        $result = $key . '=' . (!empty($value) && stristr('"', $value) ? $value : '"' . $value . '"');
                    }
                    break;
            }
        }
        return $result;
    }

    /***
     * 设置查询数据时的分组字段
     * @param string $groupBy 按某个指定字段的数据进行分组
     * @return $this
     */
    public function GroupBy(string $groupBy): DataBase
    {
        $this->_group = " GROUP BY `{$groupBy}`";
        return $this;
    }

    /**
     * 排序
     * @param $orderByField
     * @param string $orderByMode (ASC | DESC)
     * @param null $tableName
     * @return $this
     */
    public function OrderBy($orderByField, $orderByMode = 'DESC', $tableName = null): DataBase
    {
        $table = $this->getTableName($tableName);
        empty($this->_order) ? $this->_order = " ORDER BY {$table}.{$orderByField} {$orderByMode}" : $this->_order .= ",{$orderByField} {$orderByMode}";
        return $this;
    }

    public function GetOrder()
    {
        return $this->_order;
    }

    /**
     * 分页
     * @param $rowSize
     * @param $index
     * @return $this
     */
    public function Limit($rowSize, $index): DataBase
    {
        $index = intval($index);
        $rowSize = intval($rowSize);
        $this->_limit = " LIMIT " . ($index == 0 ? $rowSize : ($index - 1) * $rowSize . ',' . $rowSize);
        return $this;
    }

    /**
     * 在数据表中查询数据的所有记录
     * @param mixed $param
     * @return $this
     */
    public function Select($param = null): DataBase
    {
        if (Arrays::Is($this->_join)) {
            if (Arrays::Is($param)) {
                $new_param = [];
                foreach ($param as $key => $item) {
                    if (is_string($key)) {
                        $new_param[] = $this->getTableName($item[0]) . '.' . $item[1] . ' AS ' . $key;
                    } else {
                        $new_param[] = $this->getTableName() . '.' . $item . ' AS ' . $item;
                    }
                }
                $select = implode(',', $new_param);
            } else {
                $select = '*';
            }
        } else {
            if (Arrays::Is($param)) {
                $select = implode(',', $param);
            } elseif (is_null($param)) {
                $select = '*';
            } else {
                $select = $param;
            }
        }
        $sql = "SELECT {$select}" . $this->getFrom();
        if (Arrays::Is($this->_join)) {
            foreach ($this->_join as $item) {
                $sql .= ' ' . $item['joinType'] . ' JOIN ' . $this->getTableName($item['table']) . ' ON '
                    . $this->getTableName() . '.' . $item['condition_fromField'] . '=' . $this->getTableName($item['table']) . '.' . $item['condition_joinField'];
            }
        }
        empty($this->_where) ?: $sql .= $this->_where;
        empty($this->_order) ?: $sql .= $this->_order;
        empty($this->_group) ?: $sql .= $this->_group;
        empty($this->_limit) ?: $sql .= $this->_limit;
        $this->sql[] = $sql;
        return $this;
    }

    public function First()
    {
        $this->_toList = false;
        return $this->_queryBySelect();
    }

    public function ToList()
    {
        $this->_toList = true;
        return $this->_queryBySelect();
    }

    private function _queryBySelect()
    {
        Arrays::Is($this->sql) ?: $this->_notice('Do Query SQL is Empty');
        $class = $this->_dbInitClass[$this->_nowDBLinkIndex];
        $result = null;
        $sql_lower = strtolower($this->sql[0]);
//        logger('sql')->LOGS('sql query', $this->sql);
        if ((strstr($sql_lower, 'select') || strstr($sql_lower, 'show'))
            && method_exists($class, 'fetchAssoc')) {
            $result = $class->fetchAssoc($this->sql[0], $this->_toList, $this->_nowDBLinkIndex);
            $this->sql = [];
        }
        return $result;
    }

    /**
     * 数据表数据量的统计
     * @param string $fieldAS
     * @param string $field
     * @return int
     */
    public function Count($field = 'id', $fieldAS = 'count'): int
    {
        $sql = "SELECT COUNT({$field}) AS {$fieldAS} " . $this->getFrom();
        empty($this->_where) ?: $sql .= $this->_where;
        empty($this->_group) ?: $sql .= $this->_group;
        $this->sql[] = $sql;
        $result = $this->First();
        return !empty($result) ? (int)$result[$fieldAS] : 0;
    }

    // ============================ 数据设置

    /**
     * 检查数据表名是否已经存在
     * @param null $tableName
     * @param int $index
     * @return bool
     */
    public function CheckTableNameExists($tableName = null, $index = 0): bool
    {
        $config = $this->GetNowConfig($index);
        $db_name = $config['name'];
        $view_tableName = $this->getTableName($tableName, $index);
        $this->sql[] = <<<SQL
SELECT table_name FROM information_schema.tables WHERE table_schema="{$db_name}" AND table_name="{$view_tableName}"
SQL;
        $result = $this->First();
        return is_array($result);
    }

    /**
     * 将提交的数组转换为 insert SQL语句
     * @param array $data 被 INSERT 数组
     * @param null $table_name
     * @param bool $isBatch
     * @return $this
     */
    public function Insert($data = [], $table_name = null, $isBatch = false): DataBase
    {
        $this->_isBatch = $isBatch;
        $config = $this->connection_config[$this->_nowDBLinkIndex];
        $field = $value = '';
        if (is_object($data)) {
            $data = Arrays::ObjectToArray($data);
        }
        if (Arrays::Is($data) && isset($data[0])) {
            $valueArr = [];
            foreach ($data as $item) {
                list($field, $valueArr[]) = self::_insertOrReplaceDataJoin($item);
            }
            $value = Arrays::Is($valueArr) ? implode(",\r\n", $valueArr) : '';
        } elseif (Arrays::Is($data)) {
            list($field, $value) = self::_insertOrReplaceDataJoin($data);
        }
        if (!empty($field) && !empty($value)) {
            $table = $this->getTableName($table_name);
            if ($isBatch && is_array($value) && array_key_exists(0, $value)) {
                foreach ($value as $item) {
                    $this->sql[] = <<<SQL
INSERT INTO {$table} ({$field}) VALUES 
{$item}
SQL;

                }
            } else {
                $this->sql[] = <<<SQL
INSERT INTO {$table} ({$field}) VALUES {$value}
SQL;
            }
        }
        return $this;
    }

    /**
     * 将提交的数组转换成 UPDATE SQL 语句
     * @param array $data 被 UPDATE 的数组
     * @param null $table_name
     * @return $this
     */
    public function Update($data = [], $table_name = null): DataBase
    {
        $setArr = [];
        if (is_object($data)) {
            $data = Arrays::ObjectToArray($data);
        }
        $config = $this->connection_config[$this->_nowDBLinkIndex];
        foreach ($data as $key => $value) {
            switch (\gettype($value)) {
                case 'boolean':
                    $item = "`{$key}`=" . ($value ? "TRUE" : "FALSE");
                    break;
                default:
                    $item = "`{$key}`=" . "'" . str_replace("'", "\'", $value) . "'";
                    break;
            }
            !isset($item) ?: $setArr[] = $item;
        }
        if (Arrays::Is($setArr)) {
            $set = implode(',', $setArr);
            $table = $this->getTableName($table_name);
            $sql = "UPDATE" . " {$table} SET {$set}";
            empty($this->_where) ?: $sql .= $this->_where;
            $this->sql[] = $sql;
        }
        return $this;
    }

    /**
     * REPLACE 批量数据更新
     * @param array $data
     * @param null $table_name
     * @return $this
     */
    public function Replace($data = [], $table_name = null): DataBase
    {
        $field = $value = '';
        if (is_object($data)) {
            $data = Arrays::ObjectToArray($data);
        }
        if (Arrays::Is($data) && isset($data[0])) {
            $valueArr = [];
            foreach ($data as $item) {
                list($field, $valueArr[]) = self::_insertOrReplaceDataJoin($item);
            }
            $value = implode(',', $valueArr);
        } elseif (Arrays::Is($data)) {
            list($field, $value) = self::_insertOrReplaceDataJoin($data);
        }
        if (!empty($field) && !empty($value)) {
            $table = $this->getTableName($table_name);
            $this->sql[] = "REPLACE" . " INTO {$table} ({$field}) VALUES {$value}";
        }
        return $this;
    }

    /**
     * 组合提交的数组为 INSERT SQL 组合
     * @param array $data 被 INSERT 数组
     * @return array
     */
    private function _insertOrReplaceDataJoin($data = []): array
    {
        $fieldArr = $dataArr = [];
        $field = $value = '';
        $config = $this->connection_config[$this->_nowDBLinkIndex];
        if (Arrays::Is($data)) {
            foreach ($data as $key => $value) {
                if (empty($key)) {
                    continue;
                }
                $fieldArr[] = $key;
                $dataArr[] = str_replace("'", "\'", $value);
            }
            $field = '`' . implode('`,`', $fieldArr) . '`';
            $value = "('" . implode("','", $dataArr) . "')";
        }
        return array($field, $value);
    }

    /**
     * 删除数据表中的指定数据
     * @return $this
     */
    public function Delete(): DataBase
    {
        $sql = "DELETE {$this->getFrom()}";
        empty($this->_where) ?: $sql .= $this->_where;
        $this->sql[] = $sql;
        return $this;
    }

    /**
     * 删除表
     * @param string $name_db
     * @param string $name_table
     * @return $this
     */
    public function DropTable(string $name_db, string $name_table): DataBase
    {
        $this->sql[] = <<<SQL
DROP TABLE `{$name_db}`.`{$name_table}`
SQL;
        return $this;
    }

    /**
     * 清空数据表中的数据
     * @param string|null $table_name
     * @return $this
     */
    public function Truncate(string $table_name = null): DataBase
    {
        $table_name = is_null($table_name) ? $this->getTableName() : $this->getTableName($table_name);
        $this->sql[] = <<<SQL
TRUNCATE {$table_name}
SQL;
        return $this;
    }

    protected function getDBVer(): int
    {

        $class = $this->_dbInitClass[$this->_nowDBLinkIndex];
        if (property_exists($class, 'version')) {
            return $class->version;
        } else {
            return 0;
        }
    }

    /**
     * 执行 SQL 语句
     */
    public function Exec()
    {
        if (!Arrays::Is($this->sql)) {
            $this->_notice('Do Query SQL is Empty');
        }
        $sql_str = strtolower($this->sql[0]);
        if ($this->getDBVer() < 50530) {
            $sql_str = str_replace(['utf8mb4', 'utf8mb4_general_ci'], ['utf8', 'utf8_general_ci'], $sql_str);
        }
        $class = $this->_dbInitClass[$this->_nowDBLinkIndex];
        $result = null;
        //logger('sql')->LOGS('sql exec', $this->sql);
        if (
            strstr($sql_str, 'insert') && method_exists($class, 'insert')
            && method_exists($class, 'insertId')
        ) {
            if ($this->_isBatch) {
                $class->insert($this->sql, $this->_nowDBLinkIndex);
            } else {
                $result = $class->insertId($this->sql[0], $this->_nowDBLinkIndex);
            }
        } elseif (method_exists($class, 'sqlQuery')) {
            $result = $class->sqlQuery($this->sql[0], $this->_nowDBLinkIndex);
        }
        $this->sql = [];
        return $result;
    }

    public function BatchRun(string $str_sql = null)
    {
        if (is_null($str_sql)) {
            if (!Arrays::Is($this->sql)) {
                $this->_notice('Do Query SQL is Empty');
            }
            $str_sql = implode(';', $this->sql);
        }
        $class = $this->_dbInitClass[$this->_nowDBLinkIndex];
        if (method_exists($class, 'sqlQuery')) {
            $class->sqlQuery($str_sql, $this->_nowDBLinkIndex);
        }
    }

    /**
     * @param array $_sql
     * @return DataBase
     */
    public function SetSql(array $_sql): DataBase
    {
        $this->sql = Arrays::Is($this->sql) ? array_merge($this->sql, $_sql) : $_sql;
        return $this;
    }

    /**
     * @return array
     */
    public function GetSql(): array
    {
        $res_sql = $this->sql;
        $this->sql = [];
        return $res_sql;
    }

}
